<?php
namespace App\Admin\Controllers;


use App\Admin\Traits\statBtn;
use App\Models\Client;
use App\Models\CrmConfig;
use App\Models\MoneyOut;
use App\Models\User;
use Encore\Admin\Form\Row;
use Encore\Admin\Layout\Content;
use Encore\Admin\Widgets\Box;
use Encore\Admin\Grid;
use Carbon\Carbon;
use App\Models\Department;
use Illuminate\Support\Facades\Redis;
use \Illuminate\Support\Facades\Request;
use Illuminate\Support\Facades\DB;

class StatController extends BaseController
{
    use statBtn;
    public function index(Content $content)
    {
        return $content;
    }

    protected $level = [1=>'中心','部门','小部门','小组'];


    //region 个人呼叫排行 --- 部门呼叫排行   FINISHED

    /***
     * 个人呼叫排行
     * @param Content $content
     * @return Content
     */
    protected function call_single(Content $content)
    {
        return $content->header('呼叫次数排行')
            ->description('个人呼叫次数排行（按呼出次数统计）')
            ->row(statBtn::tabMenuList())
            ->row($this->grid_for_call_single());
    }

    /**
     * 个人呼叫排行 数据处理
     *
     * @return Grid
     */
    protected function grid_for_call_single()
    {
        $grid = new Grid(new User());
        $grid->disableCreateButton();
        $grid->disableRowSelector();
        $grid->disableActions();
        $grid->disableColumnSelector();
        $grid->disableExport();
        // 每页条数
        $grid->perPages([10,20,50,100,200,500,1000]);

        $grid->model()
            ->where('id','>',1)
            ->with('call_record')
            ->withCount(['calls','answers']);

        // 自己加序号 666
        $grid->rows(function ($row, $number) use ($grid) {
            $row->column('key', userRanking($grid , $number));
        });

        $grid->header(function ($query) use ($grid) {
            $rs = $grid->rows()->map(function ($item, $key){
                return ['name'=>$item->name,'calls_count'=>$item->calls_count,'answers_count'=>$item->answers_count];
            });

            $series['head'] = ['呼叫次数排行'];
            $series['title'] = ['calls_count'=>'呼出次数','answers_count'=>'接听次数'];
            if($rs->count() == 0){
                return new Box('图表', '暂无图表数据');
            }else{
                foreach ($rs as $k => $item){
                    $series['employee_name'][$k] = $item['name'];
                    $series['answers_count'][$k] = $item['answers_count'];
                    $series['calls_count'][$k] = $item['calls_count'];
                }
                $json = '';
                foreach ($series['title'] as $key => $title){
                    $json .= json_encode(['name'=>$title,'type'=>'bar','data'=>$series[$key]]).',';
                }
                $json = rtrim($json,',');
                $echarts = view('admin.echarts',['data'=>json_encode($series),'series'=>$json]);
                return new Box('图表', $echarts);
            }
        });

        $grid->column('key', __('排名'));
        $grid->column('name', '销售员');
        $grid->column('department_id', '部门')->display(function ($model){
            return User::myDepartment($this->id);
        });
        $grid->column('calls_count', __('呼出次数'));
        $grid->column('answers_count', __('接听次数'));
        $grid->filter(function($filter){
            // 去掉默认的id过滤器
            $filter->disableIdFilter();

            $filter->expand();
            // 在这里添加字段过滤器
            $filter->column(1/2, function ($filter) {
                $filter->between('call_record.created_at','统计时间')->datetime();
            });
            $filter->column(1/2, function ($filter) {
                $filter->equal('id', '销售员')->select(User::Users());
            });
        });

        $grid->footer(function ($query) use ($grid) {

            $answers_count = $grid->rows()->reduce(function ($answers_count,$item){
                return $answers_count + $item->answers_count;
            });
            $calls_count_sum = $grid->rows()->reduce(function ($calls_count_sum,$item){
                return $calls_count_sum + $item->calls_count;
            });
            return "<div style='padding: 10px;'>总呼出 ： $calls_count_sum   总接入： $answers_count  </div>";
        });

        $grid->paginate(10);
        return $grid;
    }


    /***
     * 部门呼叫排行
     * @param Content $content
     * @return Content
     */
    protected function call_department(Content $content)
    {
        return $content->header('呼出次数排行')
            ->description('部门呼出次数排行（按呼出次数统计）')
            ->row(statBtn::tabMenuList())
            ->row($this->grid_for_call_department());
    }

    /***
     * 部门呼叫 数据处理
     * @return Grid
     */
    protected function grid_for_call_department()
    {
        $grid = new Grid(new Department());
        $grid->disableCreateButton();
        $grid->disableRowSelector();
        $grid->disableActions();
        $grid->disableColumnSelector();
        $grid->disableExport();

        $grid->model()->with('employees.calls', 'employees.answers');



        $grid->paginate(10);
        // 自己加序号 666
        $grid->rows(function ($row, $number) use ($grid) {
            $row->column('key', userRanking($grid , $number));
        });

        $grid->header(function ($query) use ($grid) {

            $rs = $grid->rows()->map(function ($item, $key){

                $calls_count = collect($item->employees)->pluck('calls')->map(function($q){return count($q);})->sum();
                $answers_count = collect($item->employees)->pluck('answers')->map(function($q){return count($q);})->sum();

                return ['name'=>$item->title,'calls_count'=>$calls_count,'answers_count'=>$answers_count];
            });

            $series['head'] = ['呼叫次数排行'];
            $series['title'] = ['calls_count'=>'呼出次数','answers_count'=>'接听次数'];
            if($rs->count() == 0){
                return new Box('图表', '暂无图表数据');
            }else{
                foreach ($rs as $k => $item){
                    $series['employee_name'][$k] = $item['name'];
                    $series['answers_count'][$k] = $item['answers_count'];
                    $series['calls_count'][$k] = $item['calls_count'];
                }
                $json = '';
                foreach ($series['title'] as $key => $title){
                    $json .= json_encode(['name'=>$title,'type'=>'bar','data'=>$series[$key]]).',';
                }
                $json = rtrim($json,',');
                $echarts = view('admin.echarts',['data'=>json_encode($series),'series'=>$json]);
                return new Box('图表', $echarts);
            }
        });

        $grid->model()->collection(function($connection){
            foreach ($connection as $item) {
                foreach ($item->employees as $employee) {
                    $calls = isset($employee['calls']) ? count($employee['calls']) : 0;
                    $answers = isset($employee['answers']) ? count($employee['answers']) : 0;
                    $item->calls_count += isset($employee['calls']) ? count($employee['calls']) : 0;
                    $item->answers_count += isset($employee['answers']) ? count($employee['answers']) : 0;
                }
            }

            return $connection;
        });

        $grid->column('key', __('排序'));
        $grid->column('title', __('部门'))->display(function ($model){
            $secDep ='';
            if(isset($this->parent_id) && $this->parent_id > 0){
                $secDep = Department::allDepartment()[$this->parent_id].' >';
            }
            return " $secDep $model ";
        });
        $grid->column('calls_count', __('呼出次数'));
        $grid->column('answers_count', __('接听次数'));
        $grid->filter(function($filter){
            // 去掉默认的id过滤器
            $filter->disableIdFilter();
            $filter->expand();
            // 在这里添加字段过滤器
            $filter->column(1/2, function ($filter) {
                $filter->between('call_record.created_at','统计时间')->datetime();
            });
            $filter->column(1/2, function ($filter) {
                $filter->equal('level', '部门')->select($this->level);
            });
        });

        $grid->footer(function ($query) use ($grid) {

            $answers_count = $grid->rows()->reduce(function ($answers_count,$item){
                return $answers_count + $item->answers_count;
            });
            $calls_count_sum = $grid->rows()->reduce(function ($calls_count_sum,$item){
                return $calls_count_sum + $item->calls_count;
            });
            return "<div style='padding: 10px;'>总呼出 ： $calls_count_sum   总接入： $answers_count  </div>";
        });
        return $grid;
    }

//endregion


    // region 个人跟进次数排行 --- 部门跟进次数排行
    /**
     * 跟进次数排行
     * @param Content $content
     * @return Content
     */
    protected function follow_single(Content $content){
        return $content->header('跟进次数排行')
            ->description('个人跟进次数排行（按写跟进获取的时间统计）')
            ->row(statBtn::tabMenuList())
            ->row($this->grid_for_follow_single());
    }

    protected function grid_for_follow_single(){
        $grid = new Grid(new User());
        $grid->disableCreateButton();
        $grid->disableRowSelector();
        $grid->disableActions();
        $grid->disableColumnSelector();
        $grid->disableExport();
        // 每页条数
        $grid->perPages([10,20,50,100,200,500,1000]);


        $grid->model()->where('id','>',1)->withCount(['records','moneyOut']);

        // 自己加序号 666
        $grid->rows(function ($row, $number) use ($grid) {
            $row->column('key', userRanking($grid , $number));
        });

        $grid->header(function ($query) use ($grid) {
            $rs = $grid->rows()->map(function ($item, $key){
                return ['name'=>$item->name,'records_count'=>$item->records_count,'money_out_count'=>$item->money_out_count];
            });

            $series['head'] = ['跟进次数排行'];
            $series['title'] = ['records_count'=>'跟进次数','money_out_count'=>'成交客户'];
            if($rs->count() == 0){
                return new Box('图表', '暂无图表数据');
            }else{
                foreach ($rs as $k => $item){
                    $series['employee_name'][$k] = $item['name'];
                    $series['records_count'][$k] = $item['records_count'];
                    $series['money_out_count'][$k] = $item['money_out_count'];
                }
                $json = '';
                foreach ($series['title'] as $key => $title){
                    $json .= json_encode(['name'=>$title,'type'=>'bar','data'=>$series[$key]]).',';
                }
                $json = rtrim($json,',');
                $echarts = view('admin.echarts',['data'=>json_encode($series),'series'=>$json]);
                return new Box('图表', $echarts);
            }
        });

        $grid->column('key', __('排名'));
        $grid->column('name', '销售员');
        $grid->column('department_id', '部门')->display(function ($model){
            return User::myDepartment($this->id);
        })->style('text-align:center;');
        $grid->column('records_count', __('跟进次数'));
        $grid->column('money_out_count', __('贷款笔数'));
        $grid->filter(function($filter){
            // 去掉默认的id过滤器
            $filter->disableIdFilter();

            $filter->expand();
            // 在这里添加字段过滤器
            $filter->column(1/2, function ($filter) {
                $filter->between('call_record.created_at','统计时间')->datetime();
            });
            $filter->column(1/2, function ($filter) {
                $filter->equal('id', '销售员')->select(User::Users());
            });
        });

        $grid->footer(function ($query) use ($grid) {

            $records_count = $grid->rows()->reduce(function ($records_count,$item){
                return $records_count + $item->records_count;
            });
            $money_out_count = $grid->rows()->reduce(function ($money_out_count,$item){
                return $money_out_count + $item->money_out_count;
            });
            return "<div style='padding: 10px;'>总跟进次数 ： $records_count   总贷款笔数： $money_out_count  </div>";
        });

        $grid->paginate(10);
        return $grid;
    }

    protected function follow_department(Content $content){
        return $content->header('跟进次数排行')
            ->description('部门呼出次数排行（按写跟进获取的时间统计）')
            ->row(statBtn::tabMenuList())
            ->row($this->grid_for_follow_department());
    }

    protected function grid_for_follow_department(){
        $grid = new Grid(new Department());
        $grid->disableCreateButton();
        $grid->disableRowSelector();
        $grid->disableActions();
        $grid->disableColumnSelector();
        $grid->disableExport();

        $grid->model()->with('employees.records' , 'employees.moneyOut');

        $grid->paginate(10);

        $grid->rows(function ($row, $number) use ($grid) {
            $row->column('key', userRanking($grid , $number));
        });

        $grid->header(function ($query) use ($grid) {
            $rs = $grid->rows()->map(function ($item, $key){
                $records_count = $money_count = 0;
                foreach ($item->employees as $v){

                    $records_count += isset($v['records']) ? count($v['records']) : 0;
                    $money_count += isset($v['money_out']) ? count($v['money_out']) : 0;
                }
                return ['name'=>$item->title,'records_count'=>$records_count,'money_count'=>$money_count];
            });

            $series['head'] = ['跟进次数排行'];
            $series['title'] = ['records_count'=>'跟进次数','money_count'=>'成交客户'];
            if($rs->count() == 0){
                return new Box('图表', '暂无图表数据');
            }else{
                foreach ($rs as $k => $item){

                    $series['employee_name'][$k] = $item['name'];
                    $series['records_count'][$k] = $item['records_count'];
                    $series['money_count'][$k] = $item['money_count'];
                }
                $json = '';
                foreach ($series['title'] as $key => $title){
                    $json .= json_encode(['name'=>$title,'type'=>'bar','data'=>$series[$key]]).',';
                }

                $json = rtrim($json,',');
                $echarts = view('admin.echarts',['data'=>json_encode($series),'series'=>$json]);
                return new Box('图表', $echarts);
            }
        });

        // mark
        $grid->model()->collection(function($connection){
            foreach ($connection as $item) {
                foreach ($item->employees as $employee) {
                    $item->records_count += isset($employee['records']) ? count($employee['records']) : 0;
                    $item->money_count += isset($employee['moneyOut']) ? count($employee['moneyOut']) : 0;
                }
            }
            return $connection;
        });

        $grid->column('key', __('排序'));
        $grid->column('title', __('部门'))->display(function ($model){
            $secDep ='';
            if(isset($this->parent_id) && $this->parent_id > 0){
                $secDep = Department::allDepartment()[$this->parent_id].' >';
            }
            return " $secDep $model ";
        })->style('text-align:center;');
        $grid->column('records_count', __('跟进次数'));
        $grid->column('money_count', __('贷款笔数'));
        $grid->filter(function($filter){
            // 去掉默认的id过滤器
            $filter->disableIdFilter();
            $filter->expand();
            // 在这里添加字段过滤器
            $filter->column(1/2, function ($filter) {
                $filter->between('call_record.created_at','统计时间')->datetime();
            });
            $filter->column(1/2, function ($filter) {
                $filter->equal('level', '部门')->select($this->level);
            });
        });

        $grid->footer(function ($query) use ($grid) {

            $records_count = $grid->rows()->reduce(function ($records_count,$item){
                return (int)$records_count + (int)$item->records_count;
            });
            $money_count = $grid->rows()->reduce(function ($money_count,$item){
                return (int)$money_count + (int)$item->money_count;
            });
            return "<div style='padding: 10px;'>总跟进次数 ： $records_count   总贷款笔数： $money_count  </div>";
        });
        return $grid;
    }

    //endregion


    //region 跟进客户数排行

    protected function clients_single(Content $content)
    {
        return $content->header('个人跟进客户数排行')
            ->description('跟进客户数排行（按写跟进的客户数统计）')
            ->row(statBtn::tabMenuList())
            ->row($this->grid_for_clients_single());
    }

    protected function grid_for_clients_single(){

        $grid = new Grid(new User());
        $grid->disableCreateButton();
        $grid->disableRowSelector();
        $grid->disableActions();
        $grid->disableColumnSelector();
        $grid->disableExport();
        // 每页条数
        $grid->perPages([10,20,50,100,200,500,1000]);


        $grid->model()->where('id','>',1)->withCount(['moneyOut']);
        // 自己加序号 666

        $grid->rows(function ($row, $number) use ($grid) {
            $row->column('key', userRanking($grid , $number));
        });

        // 一次性把所有人的跟进客户数都拿出来
        $query_str =Request::getRequestUri();
        $cacheFile = date('Ymd',time())."_$query_str";
        $followed=[];
        if(Redis::exists($cacheFile)){
            $followed = Redis::get($cacheFile);
        }else{
            foreach (User::getAll() as $user){
                $rs = DB::select('SELECT DISTINCT client_id FROM crm_client_records WHERE employee_id = '.$user->id);
                array_push($followed,['employee_id'=>$user->id, 'follow_count'=>count($rs)]);
            }
            $r_data = json_encode($followed);
            Redis::set($cacheFile,$r_data);
        }

        $grid->header(function ($query) use ($grid, $followed) {
            $rs = $grid->rows()->map(function ($item, $key) use ($followed){
                if(!empty($followed) && is_array($followed) && count($followed) > 0){
                    foreach ($followed as $follow){
                        if($follow['employee_id'] == $item->id){
                            $item->follow_count = $follow['follow_count'];
                        }
                    }
                }
                return ['name'=>$item->name,'follow_count'=>$item->follow_count,'money_out_count'=>$item->money_out_count];
            });

            $series['head'] = ['跟进次数排行'];
            $series['title'] = ['follow_count'=>'跟进客户数','money_out_count'=>'成交客户'];
            if($rs->count() == 0){
                return new Box('图表', '暂无图表数据');
            }else{
                foreach ($rs as $k => $item){
                    $series['employee_name'][$k] = $item['name'];
                    $series['follow_count'][$k] = $item['follow_count'];
                    $series['money_out_count'][$k] = $item['money_out_count'];
                }
                $json = '';
                foreach ($series['title'] as $key => $title){
                    $json .= json_encode(['name'=>$title,'type'=>'bar','data'=>$series[$key]]).',';
                }
                $json = rtrim($json,',');
                $echarts = view('admin.echarts',['data'=>json_encode($series),'series'=>$json]);
                return new Box('图表', $echarts);
            }
        });

        $grid->column('key', __('排名'));
        $grid->column('name', '销售员');
        $grid->column('department_id', '部门')->display(function ($model){
            return User::myDepartment($this->id);
        })->style('text-align:center;');
        $grid->column('records_count', __('跟进客户数'))->display(function ($model) use ($followed){
            $id = $this->id;
            if(!empty($followed) && is_array($followed) && count($followed) > 0){
                foreach ($followed as $follow) {
                    if ($follow['employee_id'] == $id) {
                        return $follow['follow_count'];
                    }
                }
            }
        });
        $grid->column('money_out_count', __('成交客户'));
        $grid->filter(function($filter){
            // 去掉默认的id过滤器
            $filter->disableIdFilter();

            $filter->expand();
            // 在这里添加字段过滤器
            $filter->column(1/2, function ($filter) {
                $filter->between('call_record.created_at','统计时间')->datetime();
            });
            $filter->column(1/2, function ($filter) {
                $filter->equal('id', '销售员')->select(User::Users());
            });
        });

        $grid->footer(function ($query) use ($grid, $followed) {
            $records_count = $grid->rows()->reduce(function ($records_count,$model) use($followed){
                if(!empty($followed) && is_array($followed) && count($followed) > 0) {
                    foreach ($followed as $follow) {
                        if ($model->id == $follow['employee_id']) {
                            $records_count += $follow['follow_count'];
                        }
                    }
                }
                return $records_count;
            });
            $money_out_count = $grid->rows()->reduce(function ($money_out_count,$item){
                return $money_out_count + $item->money_out_count;
            });
            return "<div style='padding: 10px;'>总跟进客户数 ： $records_count   总成交客户： $money_out_count  </div>";
        });

        $grid->paginate(10);

        return $grid;
    }

    protected function clients_department(Content $content)
    {
        return $content->header('团队跟进客户数排行')
            ->description('跟进客户数排行（按写跟进的客户数统计）')
            ->row(statBtn::tabMenuList())
            ->row($this->grid_for_clients_department());
    }

    protected function grid_for_clients_department(){
        $grid = new Grid(new Department());
        $grid->disableCreateButton();
        $grid->disableRowSelector();
        $grid->disableActions();
        $grid->disableColumnSelector();
        $grid->disableExport();

        $grid->model()->with('employees.moneyOut');

        $grid->paginate(10);
        // 自己加序号 666
        $grid->rows(function ($row, $number) use ($grid) {
            $row->column('key', userRanking($grid , $number));
        });

        // 一次性把所有人的跟进客户数都拿出来
        $query_str =Request::getRequestUri();
        $cacheFile = date('Ymd',time())."_$query_str";
        $followed=[];
        if(Redis::exists($cacheFile)){
            $followed = Redis::get($cacheFile);
            $followed = json_decode($followed, true);
        }else{
            foreach (User::getAll() as $user){
                $rs = DB::select('SELECT DISTINCT client_id FROM crm_client_records WHERE employee_id = '.$user->id);
                array_push($followed,['employee_id'=>$user->id, 'follow_count'=>count($rs)]);
            }
            $r_data = json_encode($followed);
            Redis::set($cacheFile,$r_data);
        }

        $grid->header(function ($query) use ($grid,$followed) {
            $rs = $grid->rows()->map(function ($item, $key) use ($followed){
                $follow_count = $money_count = 0;
                foreach ($item->employees as $v){
                    foreach ($followed as $follow){
                        if($v['id'] == $follow['employee_id']){
                            $follow_count += $follow['follow_count'];
                        }
                    }
                    $money_count += isset($v['money_out']) ? count($v['money_out']) : 0;
                }
                return ['name'=>$item->title,'follow_count'=>$follow_count,'money_count'=>$money_count];
            });

            $series['head'] = ['跟进客户数排行'];
            $series['title'] = ['follow_count'=>'跟进客户数','money_count'=>'成交客户'];
            if($rs->count() == 0){
                return new Box('图表', '暂无图表数据');
            }else{
                foreach ($rs as $k => $item){
                    $series['employee_name'][$k] = $item['name'];
                    $series['follow_count'][$k] = $item['follow_count'];
                    $series['money_count'][$k] = $item['money_count'];
                }
                $json = '';
                foreach ($series['title'] as $key => $title){
                    $json .= json_encode(['name'=>$title,'type'=>'bar','data'=>$series[$key]]).',';
                }

                $json = rtrim($json,',');
                $echarts = view('admin.echarts',['data'=>json_encode($series),'series'=>$json]);
                return new Box('图表', $echarts);
            }
        });

        $grid->model()->collection(function($connection) use ($followed){
            foreach ($connection as $item) {
                foreach ($item->employees as $employee) {
                    foreach ($followed as $follow){
                        if($employee->id == $follow['employee_id']){
                            $item->follow_count += $follow['follow_count'];
                        }
                    }
                    $item->money_count += isset($employee['moneyOut']) ? count($employee['moneyOut']) : 0;
                }
            }
            return $connection;
        });


        $grid->column('key', __('排序'));
        $grid->column('title', __('部门'))->display(function ($model){
            $secDep ='';
            if(isset($this->parent_id) && $this->parent_id > 0){
                $secDep = Department::allDepartment()[$this->parent_id].' >';
            }
            return " $secDep $model ";
        })->style('text-align:center;');
        $grid->column('follow_count', __('跟进客户数'));
        $grid->column('money_count', __('成交客户'));
        $grid->filter(function($filter){
            // 去掉默认的id过滤器
            $filter->disableIdFilter();
            $filter->expand();
            // 在这里添加字段过滤器
            $filter->column(1/2, function ($filter) {
                $filter->between('call_record.created_at','统计时间')->datetime();
            });
            $filter->column(1/2, function ($filter) {
                $filter->equal('level', '部门')->select($this->level);
            });
        });

        $grid->footer(function ($query) use ($grid) {

            $follow_count = $grid->rows()->reduce(function ($follow_count,$item){
                return (int)$follow_count + (int)$item->follow_count;
            });
            $money_count = $grid->rows()->reduce(function ($money_count,$item){
                return (int)$money_count + (int)$item->money_count;
            });
            return "<div style='padding: 10px;'>总跟进次数 ： $follow_count   总贷款笔数： $money_count  </div>";
        });
        return $grid;
    }

    //endregion

    //region 放贷金额排行

    protected function amount_single(Content $content)
    {
        return $content->header('放贷金额排行')
            ->description('个人放贷金额排行（按审核通过的时间统计）')
            ->row(statBtn::tabMenuList())
            ->row($this->grid_for_amount_single());
    }

    protected function grid_for_amount_single(){
        $grid = new Grid(new User());
        $grid->disableCreateButton();
        $grid->disableRowSelector();
        $grid->disableActions();
        $grid->disableColumnSelector();
        $grid->disableExport();
        // 每页条数
        $grid->perPages([10,20,50,100,200,500,1000]);


        $grid->model()->where('id','>',1)->with(['moneyOut'])->withCount('moneyOut');

        // 自己加序号 666
        $grid->rows(function ($row, $number) use ($grid) {
            $row->column('key', userRanking($grid , $number));
        });

        $grid->header(function ($query) use ($grid) {
            $rs = $grid->rows()->map(function ($item, $key){
                $moa = 0;
                if($item->money_out_count > 0){
                    foreach ($item->money_out as $money){
                        $moa += $money['out_money'];
                    }
                    $moa = $moa / 10000;
                }
                return ['name'=>$item->name,'out_money'=>$moa,'money_out_count'=>$item->money_out_count];
            });

            $series['head'] = ['成交金额排行'];
            $series['title'] = ['out_money'=>'成交金额(万)','money_out_count'=>'成交笔数'];
            if($rs->count() == 0){
                return new Box('图表', '暂无图表数据');
            }else{
                foreach ($rs as $k => $item){
                    $series['employee_name'][$k] = $item['name'];
                    $series['out_money'][$k] = $item['out_money'];
                    $series['money_out_count'][$k] = $item['money_out_count'];
                }
                $json = '';
                foreach ($series['title'] as $key => $title){
                    $json .= json_encode(['name'=>$title,'type'=>'bar','data'=>$series[$key]]).',';
                }
                $json = rtrim($json,',');
                $echarts = view('admin.echarts',['data'=>json_encode($series),'series'=>$json]);
                return new Box('图表', $echarts);
            }
        });

        $grid->model()->collection(function($connection){
            foreach ($connection as $item) {
                $money = 0;
                if(count($item->moneyOut) > 0){
                    $money = $item->moneyOut->reduce(function ($money, $it){
                        return $money +=  $it->out_money;
                    });
                }
                $item->out_money += $money;
            }
            return $connection;
        });

        $grid->column('key', __('排名'));
        $grid->column('name', '销售员');
        $grid->column('department_id', '部门')->display(function ($model){
            return User::myDepartment($this->id);
        })->style('text-align:center;');

        $grid->column('out_money', __('成交金额（万）'))->display(function ($model){
            return $model > 0 ? $model / 10000 : 0;
        });
        $grid->column('money_out_count', __('成交笔数'));
        $grid->filter(function($filter){
            // 去掉默认的id过滤器
            $filter->disableIdFilter();

            $filter->expand();
            // 在这里添加字段过滤器
            $filter->column(1/2, function ($filter) {
                $filter->between('call_record.created_at','统计时间')->datetime();
            });
            $filter->column(1/2, function ($filter) {
                $filter->equal('id', '销售员')->select(User::Users());
            });
        });

        $grid->footer(function ($query) use ($grid) {
            $out_money = $grid->rows()->reduce(function ($out_money,$item){
                return $out_money + $item->out_money;
            });
            $money_out_count = $grid->rows()->reduce(function ($money_out_count,$item){
                return $money_out_count + $item->money_out_count;
            });
            return "<div style='padding: 10px;'>总成交金额 (万) ： $out_money   总成交笔数： $money_out_count  </div>";
        });

        $grid->paginate(10);
        return $grid;
    }

    protected function amount_department(Content $content)
    {
        return $content->header('放贷金额排行')
            ->description('部门放贷金额排行（按审核通过的时间统计）')
            ->row(statBtn::tabMenuList())
            ->row($this->grid_for_amount_department());
    }

    protected function grid_for_amount_department(){
        $grid = new Grid(new Department());
        $grid->disableCreateButton();
        $grid->disableRowSelector();
        $grid->disableActions();
        $grid->disableColumnSelector();
        $grid->disableExport();

        $grid->model()->with('employees.moneyOut');

        $grid->paginate(10);
        // 自己加序号 666
        $grid->rows(function ($row, $number) use ($grid) {
            $row->column('key', userRanking($grid , $number));
        });

        $grid->header(function ($query) use ($grid) {
            $rs = $grid->rows()->map(function ($item, $key){
                $out_money = $money_out_count = 0;
                foreach ($item->employees as $v){
                    $money_out_count += count($v['money_out']);
                    $out_money += collect($v['money_out'])->pluck('out_money')->sum();
                }
                $out_money = $out_money > 0 ? $out_money / 10000 : 0;
                return ['name'=>$item->title,'out_money'=>$out_money,'money_out_count'=>$money_out_count];
            });

            $series['head'] = ['放贷金额排行'];
            $series['title'] = ['out_money'=>'成交金额(万)','money_out_count'=>'成交笔数'];
            if($rs->count() == 0){
                return new Box('图表', '暂无图表数据');
            }else{
                foreach ($rs as $k => $item){
                    $series['employee_name'][$k] = $item['name'];
                    $series['out_money'][$k] = $item['out_money'];
                    $series['money_out_count'][$k] = $item['money_out_count'];
                }
                $json = '';
                foreach ($series['title'] as $key => $title){
                    $json .= json_encode([ 'name'=>$title,'type'=>'bar','data'=>$series[$key] ]).',';
                }
                $json = rtrim($json,',');
                $echarts = view('admin.echarts',['data'=>json_encode($series),'series'=>$json]);
                return new Box('图表', $echarts);
            }
        });

        $grid->model()->collection(function($connection){
            foreach ($connection as $item) {
                foreach ($item->employees as $employee) {
                    $item->out_money += collect($employee->moneyOut)->pluck('out_money')->sum();
                    $item->money_out_count += collect($employee->moneyOut)->count();
                }
            }
            return $connection;
        });

        $grid->column('key', __('排序'));
        $grid->column('title', __('部门'))->display(function ($model){
            $secDep ='';
            if(isset($this->parent_id) && $this->parent_id > 0){
                $secDep = Department::allDepartment()[$this->parent_id].' >';
            }
            return " $secDep $model ";
        })->style('text-align:center;');
        $grid->column('out_money', __('成交金额'))->display(function ($model){
            return $model / 10000;
        });
        $grid->column('money_out_count', __('成交笔数'));
        $grid->filter(function($filter){
            // 去掉默认的id过滤器
            $filter->disableIdFilter();
            $filter->expand();
            // 在这里添加字段过滤器
            $filter->column(1/2, function ($filter) {
                $filter->between('call_record.created_at','统计时间')->datetime();
            });
            $filter->column(1/2, function ($filter) {
                $filter->equal('level', '部门')->select($this->level);
            });
        });

        $grid->footer(function ($query) use ($grid) {

            $out_money = $grid->rows()->reduce(function ($out_money,$item){
                return $out_money + $item->out_money;
            });
            $money_out_count = $grid->rows()->reduce(function ($money_out_count,$item){
                return $money_out_count + $item->money_out_count;
            });
            return "<div style='padding: 10px;'>总成交金额（万） ： $out_money   总成交笔数： $money_out_count  </div>";
        });
        return $grid;
    }

    //endregion

    //region 客户金额排行

    protected function amount_client(Content $content){
        return $content->header('客户金额排行')
            ->description('客户金额排行（按贷款创建的时间统计）')
            ->row($this->grid_for_amount_client());
    }

    protected function grid_for_amount_client(){
        $grid = new Grid(new MoneyOut());
        $grid->disableCreateButton();
        $grid->disableRowSelector();
        $grid->disableActions();
        $grid->disableColumnSelector();
        $grid->disableExport();
        // 每页条数
        $grid->perPages([10,20,50,100,200,500,1000]);

        $grid->model()
            ->with('client')->orderBy('out_money','desc');

        // 自己加序号 666
        $grid->rows(function ($row, $number) use ($grid) {
            $row->column('key', userRanking($grid , $number));
        });

        $grid->header(function ($query) use ($grid) {
            $rs = $grid->rows()->map(function ($item, $key){
                return ['client_name'=>$item->client_name,'out_money'=>$item->out_money];
            });

            $series['head'] = ['成交金额排行'];
            $series['title'] = ['out_money'=>'成交金额'];
            if($rs->count() == 0){
                return new Box('图表', '暂无图表数据');
            }else{
                foreach ($rs as $k => $item){
                    $series['employee_name'][$k] = $item['client_name'];
                    $series['out_money'][$k] = $item['out_money'];
                }
                $json = '';
                foreach ($series['title'] as $key => $title){
                    $json .= json_encode(['name'=>$title,'type'=>'bar','data'=>$series[$key]]).',';
                }
                $json = rtrim($json,',');
                $echarts = view('admin.echarts',['data'=>json_encode($series),'series'=>$json]);
                return new Box('图表', $echarts);
            }
        });

        $grid->column('key', __('排名'));
        $grid->column('client_name', '客户');
        $grid->column('out_money', __('成交金额(万)'))->display(function ($model){
            return $model / 10000;
        });
        $grid->column('employee_id','销售员')->using(User::Users());
        $grid->column('foo','部门')->display(function ($model){
            return User::myDepartment($this->employee_id);
        });
        $grid->filter(function($filter){
            // 去掉默认的id过滤器
            $filter->disableIdFilter();

            $filter->expand();
            // 在这里添加字段过滤器
            $filter->column(1/2, function ($filter) {
                $filter->between('call_record.created_at','统计时间')->datetime();
            });
            $filter->column(1/2, function ($filter) {
                $filter->equal('id', '销售员')->select(User::Users());
            });
        });

        $grid->footer(function ($query) use ($grid) {
            $out_money = $grid->rows()->reduce(function ($out_money,$item){
                return $out_money + $item->out_money;
            });
            return "<div style='padding: 10px;'>总成交金额(万) ： $out_money  </div>";
        });

        $grid->paginate(10);
        return $grid;
    }

    // endregion


    //region 个人状态分析

    protected function status_single(Content $content){
        return $content->header('跟进状态漏斗分析')
            ->description('个人跟进状态漏斗分析')
            ->row(statBtn::tabMenuList())
            ->row($this->grid_for_status_single());
    }

    protected function grid_for_status_single(){
        $grid = new Grid(new User());
        $grid->disableCreateButton();
        $grid->disableRowSelector();
        $grid->disableActions();
        $grid->disableColumnSelector();
        $grid->disableExport();
        // 每页条数
        $grid->perPages([10,20,50,100,200,500,1000]);

        $grid->model()
            ->withCount([
                'clients as clients_status1'=>function($query){
                    $query->where('employee_status',1);
                },
                'clients as clients_status2'=>function($query){
                    $query->where('employee_status',2);
                },
                'clients as clients_status3'=>function($query){
                    $query->where('employee_status',3);
                },
                'clients as clients_status4'=>function($query){
                    $query->where('employee_status',4);
                },
                'clients as clients_status5'=>function($query){
                    $query->where('employee_status',5);
                },
                'clients as clients_status6'=>function($query){
                    $query->where('employee_status',6);
                },
                'clients as clients_status7'=>function($query){
                    $query->where('employee_status',7);
                },
                'clients as clients_status8'=>function($query){
                    $query->where('employee_status',8);
                },
            ]);

        // 自己加序号 666
        $grid->rows(function ($row, $number) use ($grid) {
            $row->column('key', userRanking($grid , $number));
        });

        $fs = CrmConfig::getKeyValue('follow_status');

        $grid->header(function ($query) use ($grid, $fs) {

            $rs = $grid->rows();

            $series['head'] = ['跟进状态漏斗分析'];
            $series['title'] = $fs;
            if($rs->count() == 0){
                return new Box('图表', '暂无图表数据');
            }else{
                $series['value'] = 0;

                $datas = [];
                foreach ($fs as $key => $ff){
                    array_push($datas,['name'=>$ff,'value'=>0]);
                }

                foreach ($rs->toArray() as $k => $item){
                    $datas[0]['value'] += $item->clients_status1;
                    $datas[1]['value'] += $item->clients_status2;
                    $datas[2]['value'] += $item->clients_status3;
                    $datas[3]['value'] += $item->clients_status4;
                    $datas[4]['value'] += $item->clients_status5;
                    $datas[5]['value'] += $item->clients_status6;
                    $datas[6]['value'] += $item->clients_status7;
                    $datas[7]['value'] += $item->clients_status8;
                }
                $echarts = view('admin.echarts2',['series'=>json_encode($series),'data'=>json_encode($datas)]);
                return new Box('图表', $echarts);
            }
        });


        $grid->column('key', __('排名'));
        $grid->column('name', '销售员');
        $grid->column('department_id', '部门')->display(function ($model){
            return User::myDepartment($this->id);
        })->style('text-align:center;');
        foreach ($fs as $key => $ff){
            $grid->column('clients_status'.$key, $ff);
        }
        $grid->filter(function($filter){
            // 去掉默认的id过滤器
            $filter->disableIdFilter();

            $filter->expand();
            // 在这里添加字段过滤器
            $filter->column(1/2, function ($filter) {
                $filter->between('call_record.created_at','统计时间')->datetime();
            });
            $filter->column(1/2, function ($filter) {
                $filter->equal('id', '销售员')->select(User::Users());
            });
        });

        $grid->footer(function ($query) use ($grid) {

            $clients_status1 = $grid->rows()->reduce(function ($clients_status1,$item){
                return $clients_status1 + $item->clients_status1;
            });
            $clients_status2 = $grid->rows()->reduce(function ($clients_status2,$item){
                return $clients_status2 + $item->clients_status2;
            });
            $clients_status3 = $grid->rows()->reduce(function ($clients_status3,$item){
                return $clients_status3 + $item->clients_status3;
            });
            $clients_status4 = $grid->rows()->reduce(function ($clients_status4,$item){
                return $clients_status4 + $item->clients_status4;
            });
            $clients_status5 = $grid->rows()->reduce(function ($clients_status5,$item){
                return $clients_status5 + $item->clients_status5;
            });
            $clients_status6 = $grid->rows()->reduce(function ($clients_status6,$item){
                return $clients_status6 + $item->clients_status6;
            });
            $clients_status7 = $grid->rows()->reduce(function ($clients_status7,$item){
                return $clients_status7 + $item->clients_status7;
            });
            $clients_status8 = $grid->rows()->reduce(function ($clients_status8,$item){
                return $clients_status8 + $item->clients_status8;
            });

            return "<div style='padding: 10px;'>待跟进：$clients_status1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
跟进中：$clients_status2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
暂时搁置：$clients_status3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
方案报价：$clients_status4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
补资料：$clients_status7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
异议客户：$clients_status8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
未成交：$clients_status6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
已成交：$clients_status5</div>";
        });

        $grid->paginate(10);
        return $grid;
    }

    protected function status_department(Content $content){
        return $content->header('跟进状态漏斗分析')
            ->description('团队跟进状态漏斗分析')
            ->row(statBtn::tabMenuList())
            ->row($this->grid_for_status_department());
    }

    protected function grid_for_status_department(){
        $grid = new Grid(new Department());
        $grid->disableCreateButton();
        $grid->disableRowSelector();
        $grid->disableActions();
        $grid->disableColumnSelector();
        $grid->disableExport();
        // 每页条数
        $grid->perPages([10,20,50,100,200,500,1000]);

        $grid->model()->with('employees');
        $fs = CrmConfig::getKeyValue('follow_status');
        $cacheUsers = $this->cacheEmployeeData();

        $grid->rows(function ($row, $number) use ($grid) {
            $row->column('key', userRanking($grid , $number));
        });

        $grid->header(function ($query) use ($grid, $fs) {

            $rs = $grid->rows();

            $series['head'] = ['跟进状态漏斗分析'];
            $series['title'] = $fs;
            if($rs->count() == 0){
                return new Box('图表', '暂无图表数据');
            }else{
                $series['value'] = 0;
                $datas = [];
                foreach ($fs as $key => $ff){
                    array_push($datas,['name'=>$ff,'value'=>0]);
                }

                foreach ($rs->toArray() as $k => $item){
                    $datas[0]['value'] += $item->clients_status1;
                    $datas[1]['value'] += $item->clients_status2;
                    $datas[2]['value'] += $item->clients_status3;
                    $datas[3]['value'] += $item->clients_status4;
                    $datas[4]['value'] += $item->clients_status5;
                    $datas[5]['value'] += $item->clients_status6;
                    $datas[6]['value'] += $item->clients_status7;
                    $datas[7]['value'] += $item->clients_status8;
                }
                $echarts = view('admin.echarts2',['series'=>json_encode($series),'data'=>json_encode($datas)]);
                return new Box('图表', $echarts);
            }
        });



        $grid->model()->collection(function($connection) use ($cacheUsers){
            foreach ($connection as $item){
                foreach ($item->employees as $key => $employee){
                    $rs = collect($cacheUsers)->where('id',$employee->id);
                    $item->clients_status1 += $rs->sum('clients_status1');
                    $item->clients_status2 += $rs->sum('clients_status2');
                    $item->clients_status3 += $rs->sum('clients_status3');
                    $item->clients_status4 += $rs->sum('clients_status4');
                    $item->clients_status5 += $rs->sum('clients_status5');
                    $item->clients_status6 += $rs->sum('clients_status6');
                    $item->clients_status7 += $rs->sum('clients_status7');
                    $item->clients_status8 += $rs->sum('clients_status8');
                }
            }
            return $connection;
        });

        $grid->column('key', __('排名'));
        $grid->column('title', __('部门'))->display(function ($model){
            $secDep ='';
            if(isset($this->parent_id) && $this->parent_id > 0){
                $secDep = Department::allDepartment()[$this->parent_id].' >';
            }
            return " $secDep $model ";
        })->style('text-align:center;');
        foreach ($fs as $key => $ff){
            $grid->column('clients_status'.$key, $ff);
        }
        $grid->filter(function($filter){
            // 去掉默认的id过滤器
            $filter->disableIdFilter();

            $filter->expand();
            // 在这里添加字段过滤器
            $filter->column(1/2, function ($filter) {
                $filter->between('call_record.created_at','统计时间')->datetime();
            });
            $filter->column(1/2, function ($filter) {
                $filter->equal('level', '部门')->select($this->level);
            });
        });
        $grid->paginate(10);

        $grid->footer(function ($query) use ($grid) {

            $clients_status1 = $grid->rows()->reduce(function ($clients_status1,$item){
                return $clients_status1 + $item->clients_status1;
            });
            $clients_status2 = $grid->rows()->reduce(function ($clients_status2,$item){
                return $clients_status2 + $item->clients_status2;
            });
            $clients_status3 = $grid->rows()->reduce(function ($clients_status3,$item){
                return $clients_status3 + $item->clients_status3;
            });
            $clients_status4 = $grid->rows()->reduce(function ($clients_status4,$item){
                return $clients_status4 + $item->clients_status4;
            });
            $clients_status5 = $grid->rows()->reduce(function ($clients_status5,$item){
                return $clients_status5 + $item->clients_status5;
            });
            $clients_status6 = $grid->rows()->reduce(function ($clients_status6,$item){
                return $clients_status6 + $item->clients_status6;
            });
            $clients_status7 = $grid->rows()->reduce(function ($clients_status7,$item){
                return $clients_status7 + $item->clients_status7;
            });
            $clients_status8 = $grid->rows()->reduce(function ($clients_status8,$item){
                return $clients_status8 + $item->clients_status8;
            });

            return "<div style='padding: 10px;'>待跟进：$clients_status1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
跟进中：$clients_status2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
暂时搁置：$clients_status3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
方案报价：$clients_status4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
补资料：$clients_status7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
异议客户：$clients_status8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
未成交：$clients_status6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
已成交：$clients_status5</div>";
        });

        return $grid;
    }

    public function cacheEmployeeData()
    {
        $cacheName = date('Ymd',time()).'-cacheEmployeeData-';
        if(Redis::exists($cacheName)){
            $users = Redis::get($cacheName);
        }else{
            $users = User::query()
                ->where('id','>',1)
                ->withCount([
                    'clients as clients_status1'=>function($query){
                        $query->where('employee_status',1);
                    },
                    'clients as clients_status2'=>function($query){
                        $query->where('employee_status',2);
                    },
                    'clients as clients_status3'=>function($query){
                        $query->where('employee_status',3);
                    },
                    'clients as clients_status4'=>function($query){
                        $query->where('employee_status',4);
                    },
                    'clients as clients_status5'=>function($query){
                        $query->where('employee_status',5);
                    },
                    'clients as clients_status6'=>function($query){
                        $query->where('employee_status',6);
                    },
                    'clients as clients_status7'=>function($query){
                        $query->where('employee_status',7);
                    },
                    'clients as clients_status8'=>function($query){
                        $query->where('employee_status',8);
                    },
                ])->get()->toArray();
            $users = json_encode($users);
            Redis::set($cacheName,$users);
        }
        return json_decode($users,true);
    }

    //endregion


}
